package com.bookmall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import com.bookmall.dao.StockDao;
import com.bookmall.entity.Stock;
import com.bookmall.util.DBUtil;

public class StockDaoImpl implements StockDao {

	@Override
	public boolean insert(Stock t) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "insert into stock_tb(bookid,booknum) values(?,?)";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, t.getBookId());
		ps.setInt(2, t.getBookNum());
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public boolean update(Stock t) throws SQLException {
		Connection c = com.bookmall.util.DBUtil.getConnection();
	    String sql = "update stock_tb set bookId = ?, bookNum = ? where stockId = ? and delete_flag = 0";
	    PreparedStatement ps = c.prepareStatement(sql);
	    ps.setInt(1,t.getStockId());
	    ps.setInt(2,t.getBookNum());
	    ps.setInt(3,t.getBookId());
	    int row = ps.executeUpdate();
	    DBUtil.closeConnection(c, ps, null);
	    return row == 1? true: false;
	}

	@Override
	public boolean delete(int stockId) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update stock_tb set delete_flag = 1 where stockId = ? and delete_flag = 0";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, stockId);
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public Stock selectById(int id) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = " select stockid,bookid,booknum,create_time,modify_time from stock_tb where stockid=? and delete_Flag=0";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			Stock stock = new Stock();
			stock.setBookId(rs.getInt(1));
			stock.setBookNum(rs.getInt(2));
			stock.setStockId(rs.getInt(3));
			stock.setCreateTime(rs.getTimestamp(4).toLocalDateTime());
			if (rs.getTimestamp(5) != null) {
				stock.setModifyTime(rs.getTimestamp(5).toLocalDateTime());
			}
			DBUtil.closeConnection(c, ps, rs);
			return stock;
		} else {
			DBUtil.closeConnection(c, ps, rs);
			return null;
		}	
	}

	@Override
	public List<Stock> selectByPage(int page, int pageSize) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = " select stockid,bookid,booknum,create_time,modify_time from stock_tb where delete_Flag=0 limit ?,?";
		List<Stock> stocks = new LinkedList<>();
		
		PreparedStatement ps = c.prepareStatement(sql);
		
		ps.setInt(1, (page-1)*pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBookId(rs.getInt(1));
			stock.setBookNum(rs.getInt(2));
			stock.setStockId(rs.getInt(3));
			stock.setCreateTime(rs.getTimestamp(4).toLocalDateTime());
			if (rs.getTimestamp(5) != null) {
				stock.setModifyTime(rs.getTimestamp(5).toLocalDateTime());
			}
			stocks.add(stock);
		} 
		DBUtil.closeConnection(c, ps, rs);
		return stocks.isEmpty() ? Collections.emptyList() : stocks;
	}
}
